package com.pzhu.dao.impl;

import com.pzhu.dao.UserDao;
import com.pzhu.pojo.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    private final String selectAllUserSql = "select * from tb_student;";
    private final String selectUserSql = "select * from tb_student where id=?;";
    private final String insertUserSql = "insert into tb_student values(?,?,?,?,?,?,?,?,?,?);";
    private final String deleteUserSql = "delete from tb_student where id=?;";
    private final String selectLoginCountSql = "select * from tb_usercount;";
    private final String updateLoginCountSql = "update tb_usercount set count=?;";
    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    //定义用户对象并初始化，存储查询结果
    private User userSelect = null;
    private long result;

    private PreparedStatement createConn(String sql) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library?&useSSL=false&useUnicode=true&&characterEncoding=utf8", "root", "0714");
            //创建SQL语句
            ps = conn.prepareStatement(sql);
            //执行SQL语句
            return ps;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ps;
    }

    private void closeConn() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<User> selectAllUser() {
        ArrayList<User> users = new ArrayList<>();
        try {
            //获取 PreparedPreparedStatement对象
            ps = createConn(selectAllUserSql);
            //执行SQL语句
            System.out.println(ps);
            rs = ps.executeQuery();
            //遍历结果封装数据
            while (rs.next()) {
                userSelect = new User();
                userSelect.setId(rs.getLong("id"));
                userSelect.setName(rs.getString("name"));
                userSelect.setAccount(rs.getLong("account"));
                userSelect.setPassword(rs.getString("password"));
                userSelect.setBirthday(rs.getDate("birthday"));
                userSelect.setAcademy(rs.getString("academy"));
                userSelect.setSex(rs.getBoolean("sex"));
                userSelect.setClasses(rs.getString("classes"));
                userSelect.setProfession(rs.getString("profession"));
                userSelect.setEnrollment(rs.getDate("enrollment"));
                users.add(userSelect);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("没有数据");
        } finally {
            //关闭资源
            closeConn();
        }
        return users;
    }

    @Override
    public User selectUserById(User userInPut) {
        try {
            //获取 PreparedPreparedStatement对象
            ps = createConn(selectUserSql);
            ps.setLong(1, userInPut.getId());
            //执行SQL语句
            System.out.println(ps);
            rs = ps.executeQuery();
            //遍历结果封装数据
            while (rs.next()) {
                userSelect = new User();
                userSelect.setId(rs.getLong("id"));
                userSelect.setName(rs.getString("name"));
                userSelect.setAccount(rs.getLong("account"));
                userSelect.setPassword(rs.getString("password"));
                userSelect.setBirthday(rs.getDate("birthday"));
                userSelect.setAcademy(rs.getString("academy"));
                userSelect.setSex(rs.getBoolean("sex"));
                userSelect.setClasses(rs.getString("classes"));
                userSelect.setProfession(rs.getString("profession"));
                userSelect.setEnrollment(rs.getDate("enrollment"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("用户不存在");
        } finally {
            //关闭资源
            closeConn();
        }
        if (userSelect == null) {
            return new User(-1);
        }
        return userSelect;
    }

    @Override
    public long insertUser(User user) {
        try {
            //获取 PreparedPreparedStatement对象
            ps = createConn(insertUserSql);
            ps.setLong(1, user.getId());
            ps.setString(2, user.getName());
            ps.setLong(3, user.getAccount());
            ps.setString(4, user.getPassword());
            ps.setDate(5, new Date(user.getBirthday().getTime()));
            ps.setString(6, "数学与计算机学院");
            ps.setBoolean(7, user.isSex());
            ps.setString(8, user.getClasses());
            ps.setString(9, user.getProfession());
            ps.setDate(10, new Date(user.getEnrollment().getTime()));
            System.out.println(ps);
            //执行SQL语句
            result = ps.executeUpdate();
            //遍历结果封装数据
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("添加失败");
            System.out.println(result);
            result = -1;
        } finally {
            //关闭资源
            closeConn();
        }
        System.out.println(result);
        return result;
    }

    @Override
    public long deleteUser(Long id) {
        try {
            ps = createConn(deleteUserSql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("删除失败");
        } finally {
            closeConn();
        }
        System.out.println("删除成功");
        return result;
    }

    @Override
    public long updateUser(User user) {
        try {
            //获取 PreparedPreparedStatement对象
//            ps = createConn(updateUserSql);
            ps.setLong(1, user.getId());
            ps.setString(2, user.getName());
            ps.setLong(3, user.getAccount());
            ps.setString(4, user.getPassword());
            ps.setDate(5, (Date) user.getBirthday());
            ps.setString(6, "数学与计算机学院");
            ps.setBoolean(7, user.isSex());
            ps.setString(8, user.getClasses());
            ps.setString(9, user.getProfession());
            ps.setDate(10, (Date) user.getEnrollment());
            //执行SQL语句
            result = ps.executeUpdate();
            //遍历结果封装数据
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("修改失败");
            return -1;
        } finally {
            //关闭资源
            closeConn();
        }
        System.out.println("修改成功");
        System.out.println(result);
        return result;
    }

    @Override
    public long selectLoginCount() {
        try {
            //获取 PreparedPreparedStatement对象

            ps = createConn(selectLoginCountSql);
            //执行SQL语句
            rs = ps.executeQuery();
            while (rs.next()) {
                result = rs.getLong(1);
            }
            //遍历结果封装数据
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("查询失败");
            return -1;
        } finally {
            //关闭资源
            closeConn();
        }
        return result;
    }

    @Override
    public long setLoginCount(long loginCount) {
        try {
            //获取 PreparedPreparedStatement对象
            ps = createConn(updateLoginCountSql);
            ps.setLong(1, loginCount);
            //执行SQL语句
            result = ps.executeUpdate();
            //遍历结果封装数据
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("修改失败");
            return -1;
        } finally {
            //关闭资源
            closeConn();
        }
        System.out.println("修改成功");
        System.out.println(result);
        return result;
    }
}
